﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Nova_Common;
using Nova_MOD;
using Nova_BLL;
using Nova_DAL;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data;
using System.Globalization;
using System.IO;
using System.Text;
using System.Web.Profile;

public partial class Report_JoinOutList : System.Web.UI.Page
{
    DbHelper access = new DbHelper();
    DbCommand cmd = null;
    static DataSet ds = null;
    string strSQL = "";
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {

        }
    }
    // Methods
//    protected void bind()
//    {
//        this.strSQL = @"SELECT CC,Mat_Name,'' + Mat_Code as Mat_Code ,'' +  cast(W01 as varchar)  as W01 ,StockNo,Num,YNUM,'' + Y04 as Y04,Unit_Base,Act_Num,S01,'' + S02 as S02,S03,S05,TS02,Y02,CDT,OID,Flag2,Flag3
//   FROM (SELECT 'P' CC,TBM.Mat_Name Mat_Name,TDS.Mat_Code Mat_Code,'' +  cast(TDS.W01 as varchar)  as W01,TDS.StockNo StockNo,TDS.Num Num,
//        ISNULL(TDS.Num,0)-ISNULL(TDS.Act_Num,0) as YNUM,TDS.W04 as Y04,TBM.Unit_Base as Unit_Base,TDS.Act_Num Act_Num,
//       TDS.S01 S01,TDS.S02 S02,TDS.S03 S03,TDS.S05 S05,TDS.TS02 TS02,TDS.W02 as Y02,TDS.CDT CDT,TU.vUserName as OID,
//       case TDS.Del_Flag2  when 1 then '已作废' else '' end as Flag2,case TDS.Del_Flag3 when 1 then '已完成' else '' end as Flag3 
//      FROM TDelStorage_List TDS
//     LEFT JOIN TBase_Material TBM ON TBM.Mat_Code = TDS.Mat_Code
//     LEFT OUTER JOIN tbUser TU on TU.vUserNo=TDS.OID               
//      UNION ALL  SELECT 'I' CC,TBM.Mat_Name Mat_Name,TAD.Mat_Code Mat_Code,TAD.V01 W01,TAD.StockNo StockNo,TAD.Num Num,
//      ISNULL(TAD.Num,0)-ISNULL(TAD.Act_Num,0) as YNUM,TAD.V04 as Y04,TBM.Unit_Base as Unit_Base,TAD.Act_Num Act_Num,  
//      TAD.S01 S01,TAD.S02 S02,TAD.S03 S03,TAD.S05 S05,TAD.TS02 TS02,TAD.V02 as Y02,TAD.CDT CDT,TU.vUserName as OID,
//     case TAD.Add_Flag2  when 1 then '已作废' else '' end as Flag2, case TAD.Add_Flag3 when 1 then '已完成' else '' end as Flag3 
//     FROM TAddStorage_List TAD 
//     LEFT JOIN TBase_Material TBM ON TBM.Mat_Code = TAD.Mat_Code
//    LEFT OUTER JOIN tbUser TU on TU.vUserNo=TAD.OID                ) AS BB  Where 1=1  ";
//        if (this.txtMat_Code.Text != "")//'' +  cast(TDS.W01 as varchar) as Pos_ID
//        {
//            this.strSQL = this.strSQL + " AND Mat_Code LIKE '%" + this.txtMat_Code.Text.ToString().Trim() + "%'";
//        }
//        if (this.txtMat_Name.Text != "")
//        {
//            this.strSQL = this.strSQL + " AND Mat_Name LIKE '%" + this.txtMat_Name.Text.ToString().Trim() + "%'";
//        }
//        if (this.getDataTime1.BegTime != "")
//        {
//            this.strSQL = this.strSQL + " AND CDT >= '" + this.getDataTime1.BegTime.ToString() + "'";
//        }
//        if (this.getDataTime1.EndTime != "")
//        {
//            this.strSQL = this.strSQL + " AND CDT <= '" + this.getDataTime1.EndTime.ToString() + "'";
//        }
//        if (this.ddlS05.SelectedIndex != 0)
//        {
//            this.strSQL = this.strSQL + " AND S05 = '" + this.ddlS05.SelectedValue + "'";
//        }
//        if (this.txtW01.Text != "")
//        {
//            this.strSQL = this.strSQL + " AND W01 like '%" + this.txtW01.Text.Trim() + "%'";
//        }
//        this.strSQL = this.strSQL + " ORDER BY CDT DESC";
//        this.cmd = this.access.GetSqlStringCommond(this.strSQL);
//        ds = this.access.ExecuteDataSet(this.cmd);
//        this.gv.DataSource = ds.Tables[0].DefaultView;
//        this.gv.DataBind();
//    }

    protected void bind()
    {
        this.strSQL = @"SELECT CC,Mat_Name,'' + Mat_Code as Mat_Code ,StockNo,Num,YNUM,Unit_Base,Act_Num,CDT,''+ OID AS OID
    FROM (SELECT 'P' CC,TBM.Mat_Name Mat_Name,TDS.Mat_Code Mat_Code,TDS.StockNo StockNo,TDS.Num Num,
    ISNULL(TDS.Num,0)-ISNULL(TDS.Act_Num,0) as YNUM,TBM.Unit_Base as Unit_Base,TDS.Act_Num Act_Num,
    TDS.CDT CDT,TBD.OID as OID FROM TDelStorage_List TDS
    LEFT JOIN TBase_Material TBM ON TBM.Mat_Code = TDS.Mat_Code
    LEFT JOIN TBill_DelStorage TBD on TBD.StockNo=TDS.StockNo               
    UNION ALL  SELECT 'I' CC,TBM.Mat_Name Mat_Name,TAD.Mat_Code Mat_Code,TAD.StockNo StockNo,TAD.Num Num,
    ISNULL(TAD.Num,0)-ISNULL(TAD.Act_Num,0) as YNUM,TBM.Unit_Base as Unit_Base,TAD.Act_Num Act_Num,  
    TAD.CDT CDT,TBA.OID as OID FROM TAddStorage_List TAD 
    LEFT JOIN TBase_Material TBM ON TBM.Mat_Code = TAD.Mat_Code
    LEFT JOIN TBill_AddStorage TBA on TBA.StockNo=TAD.StockNo) AS BB  Where 1=1  ";
        if (this.txtMat_Code.Text != "")//'' +  cast(TDS.W01 as varchar) as Pos_ID
        {
            this.strSQL = this.strSQL + " AND Mat_Code LIKE '%" + this.txtMat_Code.Text.ToString().Trim() + "%'";
        }
        if (this.txtMat_Name.Text != "")
        {
            this.strSQL = this.strSQL + " AND Mat_Name LIKE '%" + this.txtMat_Name.Text.ToString().Trim() + "%'";
        }
        if (this.getDataTime1.BegTime != "")
        {
            this.strSQL = this.strSQL + " AND CDT >= '" + this.getDataTime1.BegTime.ToString() + "'";
        }
        if (this.getDataTime1.EndTime != "")
        {
            this.strSQL = this.strSQL + " AND CDT <= '" + this.getDataTime1.EndTime.ToString() + "'";
        }
        this.strSQL = this.strSQL + " ORDER BY CDT DESC";
        this.cmd = this.access.GetSqlStringCommond(this.strSQL);
        ds = this.access.ExecuteDataSet(this.cmd);
        this.gv.DataSource = ds.Tables[0].DefaultView;
        this.gv.DataBind();
    }
    protected void ibtnExcel_Click(object sender, ImageClickEventArgs e)
    {
        //base.Response.Clear();
        //base.Response.BufferOutput = true;
        //base.Response.Charset = "utf-8";
        //DateTime now = DateTime.Now;
        //base.Response.AppendHeader("Content-Disposition", "attachment;filename=" + now.ToString("yyyyMMddHHmmss") + ".xls");
        //base.Response.ContentEncoding = Encoding.GetEncoding("utf-8");
        //base.Response.ContentType = "application/ms-excel";
        //this.EnableViewState = false;
        //CultureInfo formatProvider = new CultureInfo("ZH-CN", true);
        //StringWriter writer = new StringWriter(formatProvider);
        //HtmlTextWriter writer2 = new HtmlTextWriter(writer);
        //gv.RenderControl(writer2);
        //base.Response.Write(writer.ToString());
        //base.Response.End();

        //Response.Clear();
        //Response.BufferOutput = true;
        ////设定输出的字符集(就是简体中文集)
        //Response.Charset = "utf-8";
        ////导出的文件名为score.xls
        //DateTime dtime = DateTime.Now;

        //Response.AppendHeader("Content-Disposition", "attachment;filename=" + dtime.ToString("yyyyMMddHHmmss") + ".xls");
        ////指定的数据是否是ASCII或一种Unicode 变体
        //Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
        ////设置导出文件的格式
        //Response.ContentType = "application/ms-excel";
        ////关闭ViewState
        //EnableViewState = false;
        //System.Globalization.CultureInfo cultureInfo = new System.Globalization.CultureInfo("ZH-CN", true);
        //System.IO.StringWriter stringWriter = new System.IO.StringWriter(cultureInfo);
        //System.Web.UI.HtmlTextWriter textWriter = new System.Web.UI.HtmlTextWriter(stringWriter);
        //gv.RenderControl(textWriter);
        ////把HTML写回浏览器
        //Response.Write(stringWriter.ToString());
        //Response.End();
        DataTable dt = ds.Tables[0];
        Response.Clear();
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        Response.Charset = "GB2312";
        Response.AppendHeader("content-disposition", "attachment; filename=" + HttpUtility.UrlEncode("出入明细" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"));
        Response.ContentType = "application/ms-excel";

        Response.Write("物料编码\t物料名称\t单位\t出入库\t单据数量\t实出数量\t数量差\t操作用户\t操作时间");

        Response.Write(Environment.NewLine);
        foreach (DataRow dr in dt.Rows)
        {

            StringBuilder sb = new StringBuilder();

            sb.Append(dr["Mat_Code"] + "\t" + dr["Mat_Name"] + "\t" + dr["Unit_Base"] + "\t" + dr["CC"] + "\t" + dr["Num"] + "\t" + dr["Act_Num"] + "\t" + dr["YNUM"] + "\t" + dr["OID"] + "\t" + dr["CDT"]);

            Response.Write(sb.ToString());
            Response.Write(Environment.NewLine);

        }
        Response.End();
    }

    protected void ibtnSearch_Click(object sender, ImageClickEventArgs e)
    {
        this.bind();
    }

    public override void VerifyRenderingInServerForm(Control control)
    { }



    protected void gv_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        this.gv.PageIndex = e.NewPageIndex;
        this.bind();
    }
}
